﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SysInfo;
using CubeDAL;

namespace CrossQuery.ConditionQuery
{
    public class MultiSelectCondition : Condition
    {
        protected string database_name;
        protected string table_name;
        protected string field_name;
        /// <summary>
        /// Values for multi-selection
        /// </summary>
        protected List<ConditionValue> candidate_values;
        protected List<int> selected_values;

        public MultiSelectCondition() { }

        public MultiSelectCondition(string database, string table, string field)
        {
            database_name = database;
            table_name = table;
            field_name = field;
            ExtractValues();
        }

        public string Database
        {
            get { return database_name; }
            set { database_name = value; }
        }

        public string Table
        {
            get { return table_name; }
            set { table_name = value; }
        }

        public string Field
        {
            get { return field_name; }
            set { field_name = value; }
        }

        /// <summary>
        /// Return the values, which can be selected, under this condition
        /// </summary>
        public List<ConditionValue> CandidateValues
        {
            get { return candidate_values; }
        }

        public string Type { get { return "Multi"; } }

        /// <summary>
        /// Getter & Setter of selected values, 
        /// the index of selected values are stored in the list
        /// </summary>
        public List<int> SelectedValues
        {
            get { return selected_values; }
            set { selected_values = value; }
        }

        /// <summary>
        /// This function should enable override for sub-class
        /// </summary>
        /// <returns></returns>
        public virtual string ToSQLString() 
        {
            // if no value selected, just ignore this condition
            if (selected_values == null || selected_values.Count == 0)
                return "true";
            string result = "";
            string left = String.Format(" `{0}`.`{1}`", table_name, field_name);

            foreach (int index in selected_values)
            {
                if (index < candidate_values.Count) {
                    result += left + "='" + Util.EscapeSQLString(candidate_values[index].Code) + "' or ";
                    if (candidate_values[index].Code == "")
                        result += left + " is null or";
                }
            }
            result += " false";
            return result;
        }

        private void ExtractValues()
        {
            TableInfo table_info = TableInfo.GetInstance();
            candidate_values = new List<ConditionValue>();
            DatabaseFieldNode field_node = (table_info[table_name]!= null)?table_info[table_name][field_name] : null;
            if (field_node == null)
                return;
            MySQLWrapper wrapper = new MySQLWrapper(Constant.DB_SERVER, Constant.DB_DATABASE, Constant.DB_USER, Constant.DB_PASSWORD);
            if (!field_node.HasMappingTable())
            {
                string sql = String.Format("SELECT DISTINCT `{0}` FROM `{1}`.`{2}`;", field_name, database_name, table_name);
                string[] output_fields = {field_name};
                List<List<string>> result = wrapper.Select(sql, output_fields);
                foreach (var row in result)
                { 
                    candidate_values.Add(new ConditionValue(row[0], row[0]));
                }
            }
            else // having mapping column
            {
                string sql = String.Format("SELECT DISTINCT `{0}`, `ITEM` FROM `{1}`.`{2}` LEFT JOIN `{3}`.`{4}` ON `{5}`.`{6}`=`{7}`.`CODE`;"
                    , field_name, database_name, table_name, Constant.DB_MAP_DATABASE, field_node.MappingTable, table_name, field_name, field_node.MappingTable);
                string[] output_fields = {field_name, "item"};
                List<List<string>> result = wrapper.Select(sql, output_fields);
                foreach (var row in result)
                { 
                    candidate_values.Add(new ConditionValue(row[0], row[1]));
                }
            }
        }
    }
}
